{
 "cells": [
  {
   "cell_type": "markdown",
   "source": [
    "# Task-06 Mysql"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "USE shop;\r\n",
    "CREATE ;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 表格创建见test.sql"
   ],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 练习题A"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "CREATE DATABASE miji_A;\r\n",
    "\r\n",
    "USE miji_A;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT  MAX(Name) AS Employee,\r\n",
    "        MAX(Salary) AS  Salary \r\n",
    "FROM employee\r\n",
    "GROUP BY departmentid;\r\n",
    "-- ORDER BY Salary DESC;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- Query查询\r\n",
    "-- 首先将两表进行连结\r\n",
    "-- 巧妙利用MAX函数可进行字符串使用的特性，直接对连结后的表进行GROUP BY即可\r\n",
    "-- 成也MAX，败也MAX，如果多个最高工资，则会出问题\r\n",
    "SELECT MAX(D.Name) AS 'Department', MAX(E.Name) AS 'Employee', MAX(E.Salary) AS 'Salary'\r\n",
    "FROM Employee AS E\r\n",
    "LEFT JOIN Department AS D ON E.DepartmentId = D.Id\r\n",
    "GROUP BY E.DepartmentId;\r\n",
    "---还是得弄清三个连接\r\n",
    "SELECT MAX(D.Name) AS 'Department', MAX(E.Name) AS 'Employee', MAX(E.Salary) AS 'Salary'\r\n",
    "FROM Employee AS E\r\n",
    "INNER JOIN Department AS D ON E.DepartmentId = D.Id\r\n",
    "GROUP BY E.DepartmentId;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 首先将两表进行内连结\r\n",
    "-- 使用(DepartmentId,MAX(Salary))合并子查询建立->部门+最高薪酬,然后使用IN筛选\r\n",
    "-- IN谓语 可以轻松应对多个最高工资的情况，通用而实在，推荐。\r\n",
    "SELECT D.Name AS 'Department', \r\n",
    "        E.Name AS 'Employee', \r\n",
    "        E.Salary\r\n",
    "FROM Employee AS E \r\n",
    "INNER JOIN Department AS D ON E.DepartmentId = D.Id\r\n",
    "WHERE (E.DepartmentId,E.Salary) IN (SELECT DepartmentId, MAX(Salary)\r\n",
    "                  FROM Employee\r\n",
    "                  GROUP BY DepartmentId);"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 改变相邻俩学生的座位。"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "--也可以使用IF实现\r\n",
    "-- IF(判断条件，真时返回值，假时返回值)\r\n",
    "-- 这里的第一个IF的假时返回值嵌套了另一个IF\r\n",
    "SELECT (\r\n",
    "    \tIF\r\n",
    "        ( S.id%2 = 0, S.id-1, IF(S.id = S1.largest, S.id, S.id+1))\r\n",
    "       ) AS id, S.student\r\n",
    "FROM seat AS S, (SELECT COUNT(id) AS largest FROM seat) AS S1\r\n",
    "ORDER BY id ASC;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 分数排名"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-- 使用窗口专用函数DENSE_RANK,重复值同序，非重复值连续\r\n",
    "-- 在OVER条件中，使用降序排列\r\n",
    "SELECT Score, DENSE_RANK() OVER (ORDER BY Score DESC) AS 'RANK'\r\n",
    "FROM score;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 连续出现的数字"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "CREATE TABLE number (\r\n",
    " Id INTEGER NOT NULL,\r\n",
    " Num INTEGER NOT NULL,\r\n",
    " PRIMARY KEY (id)\r\n",
    " );\r\n",
    " \r\n",
    "INSERT INTO number VALUES(1, 1);\r\n",
    "INSERT INTO number VALUES(2, 1);\r\n",
    "INSERT INTO number VALUES(3, 1);\r\n",
    "INSERT INTO number VALUES(4, 2);\r\n",
    "INSERT INTO number VALUES(5, 1);\r\n",
    "INSERT INTO number VALUES(6, 2);\r\n",
    "INSERT INTO number VALUES(7, 2);\r\n",
    "SELECT DISTINCT N1.Num AS ConsecutiveNums\r\n",
    "FROM number AS N1, number AS N2, number AS N3\r\n",
    "WHERE N2.Id = N1.Id +1 AND N3.Id = N2.Id + 1\r\n",
    "\t  AND N2.Num = N1.Num AND N3.Num = N2.Num;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT DISTINCT N.Num AS ConsecutiveNums, N.id, N.prev\r\n",
    "FROM\r\n",
    "(\tSELECT Num, Id, \r\n",
    " \t\tLAG(Id, 2) OVER (PARTITION BY Num ORDER BY Id) AS prev\r\n",
    " \tFROM number\r\n",
    " ) AS N\r\n",
    " WHERE N.ID = N.prev + 2;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 树节点 "
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT id, \r\n",
    "\t   (CASE\r\n",
    "        WHEN (SELECT count(*) FROM tree) = 1 THEN 'Root'\r\n",
    "        WHEN p_id is NULL THEN 'Root'\r\n",
    "        WHEN id NOT IN (SELECT DISTINCT T1.id\r\n",
    "                        FROM tree AS T1, tree AS T2\r\n",
    "                        WHERE T1.id = T2.p_id) THEN 'Leaf'\r\n",
    "        ELSE 'Inner'\r\n",
    "        END) AS Type\r\n",
    "FROM tree;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 至少有五名直接下属的经理"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "-------经典左连接\r\n",
    "SELECT E1.Name\r\n",
    "FROM employee2 AS E1 \r\n",
    "LEFT JOIN employee2 AS E2 \r\n",
    "    ON E1.Id = E2.ManagerId\r\n",
    "GROUP BY E1.Name\r\n",
    "HAVING (COUNT(*) >= 5) ;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT Name\r\n",
    "FROM employee2\r\n",
    "WHERE Id IN (SELECT ManagerId\r\n",
    "             FROM employee2\r\n",
    "             GROUP BY ManagerId\r\n",
    "             HAVING (COUNT(*) >= 5)  \t\t\r\n",
    "\t\t\t);"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 分数排名"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "--------注意DESC\r\n",
    "SELECT Score, RANK() OVER (ORDER BY Score DESC) AS 'RANK'\r\n",
    "FROM score;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 查询回答率最高的问题"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT question_id AS 'survey_log'\r\n",
    "FROM(\r\n",
    "\tSELECT question_id, \r\n",
    "    \t(SUM(CASE WHEN 'action' like 'answer' THEN 1 ELSE 0 END)/SUM(CASE WHEN 'action' like 'show' THEN 1 ELSE 0 END)       \r\n",
    "   \t\t) AS rate\r\n",
    "    FROM survey_log\r\n",
    "    GROUP BY question_id\r\n",
    "    ORDER BY rate DESC\r\n",
    "    -- 这里只选择降序排列的第一个，即为回答率最高的\r\n",
    "    LIMIT 1\r\n",
    ") AS S;\r\n",
    "-----------子查询，多次选择\r\n",
    "---LIMIT 1;只要一行"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 各部门前3高工资的员工"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT T.Department, T.Employee, T.Salary\r\n",
    "FROM (SELECT D.Name AS 'Department', E.Name AS 'Employee', E.Salary AS 'Salary',\r\n",
    "      RANK() OVER (PARTITION BY E.DepartmentId ORDER BY E.Salary DESC) AS ranking\r\n",
    "      FROM Employee AS E INNER JOIN Department AS D ON E.DepartmentId = D.Id\r\n",
    ") AS  T\r\n",
    "WHERE T.ranking <=3;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 平面上最近距离 "
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "DROP TABLE IF EXISTS  point_2d;\r\n",
    "CREATE TABLE point_2d(\r\n",
    "id INTEGER,\r\n",
    "x INTEGER,\r\n",
    "y INTEGER);\r\n",
    "\r\n",
    "INSERT INTO point_2d VALUES(1, -1, -1);\r\n",
    "INSERT INTO point_2d VALUES(2, 0, 0);\r\n",
    "INSERT INTO point_2d VALUES(3, -1, -2);\r\n",
    "SELECT MIN(SQRT(POW((P1.x-P2.x), 2) + POW((P1.y-P2.y), 2))) AS 'shortest'\r\n",
    "FROM point_2d AS P1, \r\n",
    "point_2d AS P2\r\n",
    "WHERE P1.id <> P2.id;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# 行程和用户"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT * FROM trips;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [
    "SELECT Request_at AS 'Day',\r\n",
    "\t\tROUND(SUM(IF(Status != 'completed', 1, 0)) / COUNT(*), 2) AS 'Cancellation Rate'\r\n",
    "FROM (SELECT * FROM trips WHERE Request_at BETWEEN '2013-10-1' AND '2013-10-3') AS T\r\n",
    "INNER JOIN (SELECT * FROM Users WHERE ROLE='client') AS C ON T.Client_Id = C.Users_Id\r\n",
    "INNER JOIN (SELECT * FROM Users WHERE ROLE='driver') AS D ON T.Driver_Id = D.Users_Id\r\n",
    "WHERE C.Banned != 'Yes' AND D.Banned != 'Yes'\r\n",
    "GROUP BY Request_at;"
   ],
   "outputs": [],
   "metadata": {}
  },
  {
   "cell_type": "markdown",
   "source": [
    "# END 2021/8/29 00:01"
   ],
   "metadata": {}
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "source": [],
   "outputs": [],
   "metadata": {}
  }
 ],
 "metadata": {
  "orig_nbformat": 4,
  "language_info": {
   "name": "python"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}